How to: Create an AutoLookup query to look up values in a table or query.
Solution:
Create a select query, add the desired tables, and create joins between them.
NOTE: With an AutoLookup query, when a value is typed in the 'many' field of a 'one-to-many' relationship, data from the joined table will be looked up and typed automatically in the 'one' field of the relationship.
1) If the Database window is not active, activate the Database window.
2) Create a select query, making sure to create two or more tables with a one-to-many relationship:
a) Click the 'Queries' tab in the Database window.
Queries tab
b) Click 'New'. (The New Query dialog box appears.)
c) Select 'Design View' from the list box.
d) Click 'OK'. (The new query opens in Design view, with the Show Table dialog box open.)
e) Do one of the following:
1] Click the 'Tables' tab to list tables only.
2] Click the 'Queries' tab to list queries only.
3] Click the 'Both' tab to list both tables and queries.
f) Select the tables and/or queries to use for the new query.
NOTE: To select more than one adjacent table/query, press and hold down SHIFT while selecting the tables/queries. To select more than one non-adjacent table/query, press and hold down CTRL while selecting the tables/queries.
g) Click 'Add'. (The selected tables and queries appear in the Query Design window behind the Show Table dialog box.)
h) Click 'Close' to close the 'Show Table' dialog box.
i) Join the tables:
NOTE: If there are default relationships already defined between any of the tables, Access automatically displays their join lines. Access can also automatically create joins between two fields with the same name and data type in two different tables (but only if one of the fields is a primary key).
1] Click on the primary key field in one table.
NOTE: This field (i.e., the join field on the 'one' side of the relationship) MUST have a unique index (i.e., it must be a primary key).
2] Drag the primary key field from that table on top of the foreign key field of the table to which it is to be joined.
NOTE: The foreign key field in the second table must be the same data type as the primary key field in the first table.
3] Release the mouse button. (A join line appears from the first table to the second table, with a '1' at the first table and an infinity sign at the second table, designating a one-to-many relationship.)
j) Add fields to the query:
NOTE: The field used in the one-to-many join on the 'many' side of the relationship MUST be included in the query. The 'many' side is the side the field was dragged TO (i.e., the second table); there should be an infinity symbol at the 'many' side of the join line.
1] Select the foreign key field from the table that is the 'many' side of the relationship in the top half of the Query Design window.
NOTE 1: This key CANNOT be a primary key.
NOTE 2: This key's 'Indexed' property CANNOT be 'Yes (No Duplicates)'.
2] Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
3] Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
4] Repeat steps 2)j)1] through 2)j)3] for each field to add to the query.
NOTE: Fields can also be added to the query by selecting a field from the drop-down list box in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
k) (Optional) If desired, specify criteria in the 'Criteria' and 'or' rows.
3) Select the 'File' menu and select 'Save' to save the query. (The Save As dialog box appears.)
4) Type a name for the query in the 'Query Name' box.
5) Click 'OK'. (The query just created is saved.)
6) To view the results of the query just created, select the 'View' menu and select 'Datasheet'.
